﻿<cfsilent>
	<cfscript>
	
		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		
		subjectId = event.getArg("SBJ");
		grade = event.getArg("GRD");
		
		/* 读取指定专业、年级待审查毕业生信息 */
		sql = "SELECT 
				  a.date_created, a.date_review, 
				  a.is_passed, a.comp_passed, a.review_passed, 
				  b.stu_id, b.stu_name, 
				  c.credit, c.credit_req, c.credit_opt, c.credit_phy, c.credit_com, c.credit_failure, c.credit_grad, 
				  c.credit_avgpt, c.credit_req_avgpt, c.credit_grad_avgpt, 
				  d.cls_name, 
				  e.sbj_name, 
				  f.institute_name, 
				  g.tch_name 
				FROM t_grad_review a 
					INNER JOIN t_student b ON b.stu_id = a.stu_id 
					INNER JOIN t_student_profile c ON c.stu_id = a.stu_id 
					INNER JOIN t_class d ON d.cls_id = b.cls_id 
					INNER JOIN t_subject e ON e.sbj_id = d.sbj_id 
					INNER JOIN t_institute f ON f.institute_id = e.institute_id 
          			INNER JOIN t_teacher g ON g.tch_id = a.tch_id 
				WHERE 
					d.grade = :grade 
					AND 
					d.sbj_id = :subjectId 
				ORDER BY 
					d.cls_name, b.stu_id ";
		
		queryObj = new Query( datasource=application.dnsSlave );
		queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
		queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );

		rs_summary = queryObj.execute( sql=sql ).getResult();

		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat( rs_summary.sbj_name & "-" & grade & "-毕业生预审结果", "utf-8" );
		
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("毕业审核总览", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学院,专业,年级,班级,学号,姓名,总学分,学位课,必修,限选,任选,体育,不及格,必修课平均绩点,全部课程平均绩点,学位课平均绩点,审核结论,自动审核,人工审核,初审时间,复审时间,审核人");
		
		/*添加数据*/
		for ( a=1; a LTE rs_summary.recordCount; a++){

			isPassed = ""; /* 毕业审核是否通过 */
			computerPassed = ""; /* 机器审核是否通过 */
			reviewPassed = ""; /* 人工审核是否通过 */
			

			if ( rs_summary["is_passed"][a] EQ 1 ){
				isPassed = "Y";	
			} 
			else {
				isPassed = "N";	
			}

			if ( rs_summary["comp_passed"][a] EQ 1 ){
				computerPassed = "Y";	
			} 
			else {
				computerPassed = "N";	
			}

			if ( rs_summary["review_passed"][a] EQ 1 ){
				reviewPassed = "Y";	
			} 
			else {
				reviewPassed = "N";	
			}
			
			/* 构建审核总览信息 */
			rowData = rs_summary["institute_name"][a] 
						& "," & rs_summary["sbj_name"][a]
						& "," & grade
						& "," & rs_summary["cls_name"][a]
						& "," & rs_summary["stu_id"][a]
						& "," & rs_summary["stu_name"][a]
						& "," & rs_summary["credit"][a]
						& "," & rs_summary["credit_grad"][a]
						& "," & rs_summary["credit_req"][a]
						& "," & rs_summary["credit_opt"][a]
						& "," & rs_summary["credit_com"][a]
						& "," & rs_summary["credit_phy"][a]
						& "," & rs_summary["credit_failure"][a]
						& "," & rs_summary["credit_req_avgpt"][a]
						& "," & rs_summary["credit_avgpt"][a]
						& "," & rs_summary["credit_grad_avgpt"][a]
						& "," & isPassed
						& "," & computerPassed
						& "," & reviewPassed
						& "," & datetimeAdvice.formatDateTimeString( rs_summary["date_created"][a], "YYYY-MM-DD HH:MI")
						& "," & datetimeAdvice.formatDateTimeString( rs_summary["date_review"][a], "YYYY-MM-DD HH:MI")
						& "," & rs_summary["tch_name"][a];
			
			/* 写入审核总览信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
		}
		
		/* 创建审核明细 */
		excel.SpreadsheetCreateSheet (spreadsheetObj, "审核条件明细");
		excel.SpreadsheetSetActiveSheet (spreadsheetObj, "审核条件明细");
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学院,专业,年级,班级,学号,姓名,审核条件,是否通过,审核明细,审核人,审核时间");
		
		/*根据年级专业读取毕业生审核明细信息*/
		sql = "SELECT  
					d.cls_name, 
        			b.stu_id, b.stu_name, 
		    		a.is_passed, a.gdc_params, a.result_descript, a.date_created, 
					c.gdc_descript, 
					e.sbj_name, 
					f.institute_name,
        			g.tch_name 
				FROM t_grad_details a 
          			INNER JOIN t_student b ON b.stu_id = a.stu_id 
					INNER JOIN t_grad_condition c ON c.gdc = a.gdc 
					INNER JOIN t_class d ON d.cls_id = b.cls_id
					INNER JOIN t_subject e ON e.sbj_id = d.sbj_id
					INNER JOIN t_institute f ON f.institute_id = e.institute_id 
          			INNER JOIN t_teacher g ON g.tch_id = a.tch_id 
        		WHERE
					d.grade = :grade 
					AND 
					d.sbj_id = :subjectId 
				ORDER BY 
					d.cls_name,stu_id,is_passed ";
		
		queryObj = new Query( datasource=application.dnsSlave );
		
		queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
		queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );

		rs_detail = queryObj.execute( sql=sql ).getResult();
		
		
		/*添加数据*/
		for ( a=1; a LTE rs_detail.recordCount; a++){
			
			/*判断是否通过*/
			isPassed = "";
			
			if ( rs_detail["is_passed"][a] EQ 1 ) {
				isPassed = "Y";	
			} 
			else {
				isPassed = "N";	
			}

			/* 构建审核明细信息 */
			rowData = rs_detail["institute_name"][a]
						& "," & rs_detail["sbj_name"][a]
						& "," & grade
						& "," & rs_detail["cls_name"][a]
						& "," & rs_detail["stu_id"][a]
						& "," & rs_detail["stu_name"][a]
						& "," & rs_detail["gdc_descript"][a]
						& "," & isPassed
						& "," & replace(rs_detail["result_descript"][a],",","，","ALL")
						& "," & rs_detail["tch_name"][a]
						& "," & datetimeAdvice.formatDateString(left(rs_detail["date_created"][a],8),"YYYY-MM-DD");
			
			/* 写入审核明细信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
		}
		
		
		/* 创建学生未通过必修课 */
		excel.SpreadsheetCreateSheet (spreadsheetObj, "未通过必修课");
		excel.SpreadsheetSetActiveSheet (spreadsheetObj, "未通过必修课");
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学院,专业,年级,班级,学号,姓名,课程名称,课程学分");
		
		/*读取未通过必修课程*/
		sql = "SELECT  e.institute_name,
					   c.cls_name,
					   a.stu_id,a.stu_name,
					   f.course_name,f.course_credit,
					   b.gdc_descript,
					   d.sbj_name
				FROM t_mark_need t 
					 INNER JOIN t_student a ON a.stu_id = t.stu_id 
					 INNER JOIN t_grad_condition b ON b.gdc = t.gdc 
					 INNER JOIN t_class c ON c.cls_id = a.cls_id 
					 INNER JOIN t_subject d ON d.sbj_id = c.sbj_id 
					 INNER JOIN t_institute e ON e.institute_id = d.institute_id 
					 INNER JOIN t_course f ON f.cid = t.cid 
				WHERE 
					   t.gdc = 'CPR' 
					   AND 
					   c.grade = :grade  
					   AND 
				       c.sbj_id = :subjectId 
				ORDER BY 
					   c.cls_name,stu_id";
									  
		queryObj = new Query( datasource=application.dnsSlave );
		queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
		queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
		
		rs_student_required = queryObj.execute( sql=sql ).getResult();
		
		
		/*添加数据*/
		for ( a=1; a LTE rs_student_required.recordCount; a++){
			
			/* 构建未通过必修课信息 */
			rowData = rs_student_required["institute_name"][a]
						& "," & rs_student_required["sbj_name"][a]
						& "," & grade
						& "," & rs_student_required["cls_name"][a]
						& "," & rs_student_required["stu_id"][a]
						& "," & rs_student_required["stu_name"][a]
						& "," & rs_student_required["course_name"][a]
						& "," & rs_student_required["course_credit"][a];
			
			/* 写入未通过必修课信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
		}
		
		excel.SpreadsheetSetActiveSheet (spreadsheetObj, "毕业审核总览");
		
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
		
	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />	

</cfsilent>